/**********************************************************************************************
Do-file:		Summary statistics.do
Date:			29th September 2020
Author:			Jennifer Mayo
Description:	Produces summary stats tables (Table 1 and Table C.2)
***********************************************************************************************/


clear
set more off

global name "jmp4k"
global home "/Users/$name/Library/CloudStorage/OneDrive-UniversityofMissouri/Projects/Navigating the notches"
global data "$home/Replication"
global output "$home/CN Output"

use "$dataCN/Overall score.dta", clear

***********************************************************************************************

merge m:m ein ratingid using "$data/Fin and A&T scores.dta", keepusing(finscore accountscore)
drop if _merge==2
drop _merge  

merge m:m ein ratingid using "$data/Form990objects.dta", keepusing(totalrevenue administrativeexpenses fundraisingexpenses programexpenses totalexpenses totalcontributions totalnetassets)
drop if _merge==2
drop _merge 

rename PublicationYear year
merge m:m year using "$data/CPI.dta", keepusing(annualcpi)
drop if _merge==2
drop _merge 
rename year PublicationYear
** Make 2010 the base year
gen cpitemp = annualcpi if PublicationYear==2010
egen cpitemp2 = max(cpitemp)
gen cpideflate = annualcpi/cpitemp2

foreach var of varlist totalcontributions-totalrevenue {
	gen `var'_mil = `var'/1000000
}


foreach var of varlist totalcontributions_mil-totalrevenue_mil {
	gen `var'_real = `var'*cpideflate
}

** Merge NTEE classifications

merge m:m ein using "$data/Overall score.dta", ///
keepusing(ntee_decile ntee_centile ntee_majorgroup)  
drop if _merge==2
drop _merge

duplicates drop

replace ntee_majorgroup = "A: Arts, Culture and Humanities" if ntee_majorgroup =="A"
replace ntee_majorgroup = "B: Education" if ntee_majorgroup =="B"
replace ntee_majorgroup = "C: Environment" if ntee_majorgroup =="C"
replace ntee_majorgroup = "D: Animal-Related" if ntee_majorgroup =="D"
replace ntee_majorgroup = "E: Health Care" if ntee_majorgroup =="E"
replace ntee_majorgroup = "F: Mental Health and Crisis Intervention" if ntee_majorgroup =="F"
replace ntee_majorgroup = "G: Medicine and Voluntary Health Associations" if ntee_majorgroup =="G"
replace ntee_majorgroup = "H: Medical Research" if ntee_majorgroup =="H"
replace ntee_majorgroup = "I: Crime and Legal-Related" if ntee_majorgroup =="I"
replace ntee_majorgroup = "J: Employment" if ntee_majorgroup =="J"
replace ntee_majorgroup = "K: Food, Agriculture and Nutrition" if ntee_majorgroup =="K"
replace ntee_majorgroup = "L: Housing and Shelter" if ntee_majorgroup =="L"
replace ntee_majorgroup = "M: Public Safety, Disaster Preparedness and Relief" if ntee_majorgroup =="M"
replace ntee_majorgroup = "N: Recreation and Sports" if ntee_majorgroup =="N"
replace ntee_majorgroup = "O: Youth Development" if ntee_majorgroup =="O"
replace ntee_majorgroup = "P: Human Services" if ntee_majorgroup =="P"
replace ntee_majorgroup = "Q: International, Foreign Affairs and National Security" if ntee_majorgroup =="Q"
replace ntee_majorgroup = "R: Civil Rights, Social Action and Advocacy" if ntee_majorgroup =="R"
replace ntee_majorgroup = "S: Community Improvement and Capacity Building" if ntee_majorgroup =="S"
replace ntee_majorgroup = "T: Philanthropy, Voluntarism and Grantmaking" if ntee_majorgroup =="T"
replace ntee_majorgroup = "U: Science and Technology Research Institutes" if ntee_majorgroup =="U"
replace ntee_majorgroup = "V: Social Science Research Institutes" if ntee_majorgroup =="V"
replace ntee_majorgroup = "W: Public and Society Benefit"  if ntee_majorgroup =="W"
replace ntee_majorgroup = "X: Religion-Related" if ntee_majorgroup =="X"
replace ntee_majorgroup = "Y: Mutual and Membership Benefit" if ntee_majorgroup =="Y"


****************************************************
********************** TABLES **********************
****************************************************

*****************
**** TABLE 1 ****
*****************

estpost sum score finscore accountscore totalnetassets_mil_real totalrevenue_mil_real totalcontributions_mil_real totalexpenses_mil_real programexpenses_mil_real administrativeexpenses_mil_real fundraisingexpenses_mil_real
est store A


esttab A using "$output/CNsummary.tex", ///
		replace cells(mean(fmt(%9.2fc)) sd(fmt(%9.2fc))) label ///
		mlabels("Mean" "Std Dev") ///
		varlabels(score "Raw score" finscore "Financial Health score" accountscore "Accountability \& Transparency score" ///
		totalnetassets_mil_real "Net assets" totalrevenue_mil_real "Revenue" ///
		totalcontributions_mil_real "Contributions" totalexpenses_mil_real ///
		"Total expenses" programexpenses_mil_real "Program expenses" administrativeexpenses_mil_real "Administrative expenses" ///
		fundraisingexpenses_mil_real "Fundraising expenses") ///
		collabels(none) nonote nonumbers eqlabels(none)  ///
		title(Summary Statistics for Rated Charities) ///
		addnotes("Notes: All dollars are constant (\$2010)" "Standard deviations are in parentheses") 

bys ein: gen numberofyears = _N 
sum numberofyears

bys PublicationYear: gen numberofcharities = _N
sum numberofcharities


******************
**** TABLE C2 ****
******************

estpost tabulate ntee_majorgroup if temp==1 
est store A

esttab A using "$output/CNsectors.tex", ///
		replace cells(b) label  ///
		mlabels("Rated charities") ///
		collabels(none) nonote nonumbers noobs eqlabels(none)  ///
		varlabels(`e(labels)') ///
		title(NTEE Classification of Rated Charities)
		

 		
		
		
